/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package provadb;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Mark
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here
        Main m = new Main();
        m.doStuff();



    }
    private Connection con;

    /**
     * Crea una nuova istanza della classe.
     * @param url l'indirizzo del DBMS comprensivo di porta e nome del
     * database.
     * @param username il nome dell'utente che ha i privilegi di accesso
     * al database.
     * @param password la password dell'utente che ha i privilegi di accesso
     * al database.
     * @throws SQLException se si verifica un problema durante la connessione
     * al DBMS, causata ad esempio da url, username o pasword errati oppure
     * dal fatto che il DBMS non e' attivo su quella porta.
     */
    public Connection dbBridge(String url, String username, String password)
            throws SQLException {
        Driver driver = new com.mysql.jdbc.Driver();
        DriverManager.registerDriver(driver);
        con = DriverManager.getConnection(url, username, password);
        con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        return con;
    }

    public void doStuff() {

        String url = "jdbc:mysql://sql09.freemysql.net/";
        String dbName = "provadb";
        String userName = "maktorino";
        String password = "xqnBm==P";
        try {
            this.con = dbBridge(url + dbName, userName, password);
            System.out.println("URL: " + url);
            System.out.println("Connection: " + con);
            dropTable(con, "myTable");
            Statement stmt = con.createStatement();
            stmt.executeUpdate(
                    "CREATE TABLE myTable(test_id int,"
                    + "test_val char(15) not null)");
            stmt.executeUpdate(
                    "INSERT INTO myTable(test_id, "
                    + "test_val) VALUES(1,'One')");
            stmt.executeUpdate(
                    "INSERT INTO myTable(test_id, "
                    + "test_val) VALUES(2,'Two')");
            stmt.executeUpdate(
                    "INSERT INTO myTable(test_id, "
                    + "test_val) VALUES(3,'Three')");
            stmt.executeUpdate(
                    "INSERT INTO myTable(test_id, "
                    + "test_val) VALUES(4,'Four')");
            stmt.executeUpdate(
                    "INSERT INTO myTable(test_id, "
                    + "test_val) VALUES(5,'Five')");
            stmt = con.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = stmt.executeQuery("SELECT * "
                    + "from myTable ORDER BY test_id");
            System.out.println("Display all results:");
            while (rs.next()) {
                int theInt = rs.getInt("test_id");
                String str = rs.getString("test_val");
                System.out.println("\ttest_id= " + theInt
                        + "\tstr = " + str);
            }//end while loop

            con.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void dropTable(Connection con, String tableName) {
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("DROP TABLE " + tableName);
        } catch (Exception e) {
            System.out.println(e);
            System.out.println(
                    "No existing table to delete");
        }//end catch
    }
}
